Public Class AudiometryAbnormalResultHistory
    Protected _year As String
    Protected _police As New Police()
    Protected _organization As New Organization()
    Protected _l_ear_abnormal_id As New AudiometryAbnormal()
    Protected _r_ear_abnormal_id As New AudiometryAbnormal()

    Public Property year() As String
        Get
            Return _year
        End Get
        Set(ByVal value As String)
            _year = value
        End Set
    End Property

    Public Property police() As Police
        Get
            Return _police
        End Get
        Set(ByVal value As Police)
            _police = value
        End Set
    End Property

    Public Property organization() As Organization
        Get
            Return _organization
        End Get
        Set(ByVal value As Organization)
            _organization = value
        End Set
    End Property

    Public Property l_ear_abnormal_id() As AudiometryAbnormal
        Get
            Return _l_ear_abnormal_id
        End Get
        Set(ByVal value As AudiometryAbnormal)
            _l_ear_abnormal_id = value
        End Set
    End Property

    Public Property r_ear_abnormal_id() As AudiometryAbnormal
        Get
            Return _r_ear_abnormal_id
        End Get
        Set(ByVal value As AudiometryAbnormal)
            _r_ear_abnormal_id = value
        End Set
    End Property

    Public Sub deletePoliceAudiometryAbnormalResultHistory _
        (ByVal inAudiometryResultHistory As AudiometryResultHistory)
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_DELETE_PoliceAudiometryAbnormalStatusHistory"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", _
                inAudiometryResultHistory.police.citizen_id)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inAudiometryResultHistory.year)
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Function insertPoliceAudiometryAbnormalStatusHistory _
    (ByVal inAudiometryResultHistory As AudiometryResultHistory, ByVal inType As Short) As Boolean
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_INSERT_AudiometryAbnormalStatusHistory"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", _
                inAudiometryResultHistory.police.citizen_id)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inAudiometryResultHistory.year)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", _
                Utilities.SendToDatabase(inAudiometryResultHistory.police.organization.organization_id))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationNameTha", _
                Utilities.SendToDatabase(inAudiometryResultHistory.police.organization.organization_name_tha))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inType", inType)
            objCmd.Parameters.Add(objParam)

            Dim avg_LeftdB, avg_RightdB As Double
            Select Case inType
                Case 1
                    avg_LeftdB = (inAudiometryResultHistory.dB_left_500 + _
                        inAudiometryResultHistory.dB_left_1000 + inAudiometryResultHistory.dB_left_2000) / 3
                    objParam = New MySql.Data.MySqlClient.MySqlParameter("inRightAudiometryAbnormalID", 0)
                    objCmd.Parameters.Add(objParam)
                    Select Case avg_LeftdB
                        Case 0 To 25
                            objParam = New MySql.Data.MySqlClient.MySqlParameter("inLeftAudiometryAbnormalID", 1)
                            objCmd.Parameters.Add(objParam)
                            objConn.Open()
                            objCmd.ExecuteNonQuery()
                            objConn.Close()
                            Return True
                        Case 26 To 40
                            objParam = New MySql.Data.MySqlClient.MySqlParameter("inLeftAudiometryAbnormalID", 2)
                            objCmd.Parameters.Add(objParam)
                            objConn.Open()
                            objCmd.ExecuteNonQuery()
                            objConn.Close()
                            Return False
                        Case 41 To 55
                            objParam = New MySql.Data.MySqlClient.MySqlParameter("inLeftAudiometryAbnormalID", 3)
                            objCmd.Parameters.Add(objParam)
                            objConn.Open()
                            objCmd.ExecuteNonQuery()
                            objConn.Close()
                            Return False
                        Case 56 To 70
                            objParam = New MySql.Data.MySqlClient.MySqlParameter("inLeftAudiometryAbnormalID", 4)
                            objCmd.Parameters.Add(objParam)
                            objConn.Open()
                            objCmd.ExecuteNonQuery()
                            objConn.Close()
                            Return False
                        Case 71 To 90
                            objParam = New MySql.Data.MySqlClient.MySqlParameter("inLeftAudiometryAbnormalID", 5)
                            objCmd.Parameters.Add(objParam)
                            objConn.Open()
                            objCmd.ExecuteNonQuery()
                            objConn.Close()
                            Return False
                        Case Is > 90
                            objParam = New MySql.Data.MySqlClient.MySqlParameter("inLeftAudiometryAbnormalID", 6)
                            objCmd.Parameters.Add(objParam)
                            objConn.Open()
                            objCmd.ExecuteNonQuery()
                            objConn.Close()
                            Return False
                    End Select
                Case 2
                    avg_RightdB = (inAudiometryResultHistory.dB_right_500 + _
                        inAudiometryResultHistory.dB_right_1000 + inAudiometryResultHistory.dB_right_2000) / 3
                    objParam = New MySql.Data.MySqlClient.MySqlParameter("inLeftAudiometryAbnormalID", 0)
                    objCmd.Parameters.Add(objParam)
                    Select Case avg_RightdB
                        Case 0 To 25
                            objParam = New MySql.Data.MySqlClient.MySqlParameter("inRightAudiometryAbnormalID", 1)
                            objCmd.Parameters.Add(objParam)
                            objConn.Open()
                            objCmd.ExecuteNonQuery()
                            objConn.Close()
                            Return True
                        Case 26 To 40
                            objParam = New MySql.Data.MySqlClient.MySqlParameter("inRightAudiometryAbnormalID", 2)
                            objCmd.Parameters.Add(objParam)
                            objConn.Open()
                            objCmd.ExecuteNonQuery()
                            objConn.Close()
                            Return False
                        Case 41 To 55
                            objParam = New MySql.Data.MySqlClient.MySqlParameter("inRightAudiometryAbnormalID", 3)
                            objCmd.Parameters.Add(objParam)
                            objConn.Open()
                            objCmd.ExecuteNonQuery()
                            objConn.Close()
                            Return False
                        Case 56 To 70
                            objParam = New MySql.Data.MySqlClient.MySqlParameter("inRightAudiometryAbnormalID", 4)
                            objCmd.Parameters.Add(objParam)
                            objConn.Open()
                            objCmd.ExecuteNonQuery()
                            objConn.Close()
                            Return False
                        Case 71 To 90
                            objParam = New MySql.Data.MySqlClient.MySqlParameter("inRightAudiometryAbnormalID", 5)
                            objCmd.Parameters.Add(objParam)
                            objConn.Open()
                            objCmd.ExecuteNonQuery()
                            objConn.Close()
                            Return False
                        Case Is > 90
                            objParam = New MySql.Data.MySqlClient.MySqlParameter("inRightAudiometryAbnormalID", 6)
                            objCmd.Parameters.Add(objParam)
                            objConn.Open()
                            objCmd.ExecuteNonQuery()
                            objConn.Close()
                            Return False
                    End Select
            End Select
        Catch ex As Exception
            Dim msgWarnning As New ManageBiochemistryResultHistoryControl()
            msgWarnning.showInsertBiochemistryAbnormalStatusHistoryError()
        End Try
    End Function

End Class
